Ad Effectiveness: Analysis of Ad Interactivity and Location¶

Github link: https://github.com/hjeffreywang/Ad-Effectiveness-Report

=========================================================================================================================¶

Work below¶

Scenario¶

We are given data from ad sites such as google and facebook, how can these metrics be used to create actionable insights for the rest of the teams? Done in presentation form for ease of process.

Goal¶

ABCJewelry wishes to increase sales while also reducing ad costs. Our job is to find actionable insights to help with those decisions.

Key Tasks¶

  1. Data Acquisition and Exploration
  2. Feature Processing
  3. Data Analysis
  4. Data Engineering
  5. Visualization Development

Deliverables¶

  1. How effective are our ads? (appearances, clicks, and Clicks/Appearance ratio)
  2. Which ads are best? (Campaign, Site, Platform)
  3. How long before ads become ineffective (# of appearances vs length of time)
  4. Where are our audience? (Most campaigns, most appearances, most clicks)

Feature definitions¶

  • campaign_item_id : unique id of each adevertising campaign
  • no_of_days : number of days campaign has been running
  • time : timestamp on which the data was captured
  • ext_service_id : id of each advertising platforms used
  • ext_service_name : name of each advertising platforms used
  • creative_id : id of the creative images used for ads
  • creative_height : height of the creative image for the ad in pixels
  • creative_width : width of the creative image for the ad in pixels
  • search_tags : search tags used for displaying ads
  • template_id : template used in the creative image
  • landing_page : landing page url on which users clicked or browsed through
  • advertiser_id : id of the advertiser
  • advertiser_name : name of the place of the advertiser ( city , country , state )
  • network_id : id of the each agency
  • advertiser_currency : currency of the country in which the advertiser operates in
  • channel_id : id of each channel used for placed ads
  • channel_name : name of the channel ( display , search , social , mobile video )
  • max_bid_cpm : maximum value of bid for optimizing cpm
  • campaign_budget_usd : overall budget of the campaign or the amount of money that the campaign can spend
  • impressions : the number of times an advertisement is displayed on a website or social media platform.
  • clicks : the number of times an advertisement is clicked on by a user, leading them to the advertiser's website or landing page.
  • currency_code : the currency code of the advertiser
  • exchange_rate : a relative price of one currency expressed in terms of another currency.
  • media_cost_usd : the amount of money that the campaign has spent on that particuar day
  • position_in_content : position where the ad was placed on the website page
  • unique_reach : the number of unique users who see your post or page.
  • total_reach : the number of people who saw any content from your page or about your page.
  • search_tags : a word or set of words a person enters when searching on Google or one of our Search Network sites.
  • cmi_currency_code : campaign currency code
  • time_zone : timezone in which the campaign is running
  • weekday_cat : weekday / weekend catgeory
  • keywords : a word or set of words that Google Ads advertisers can add to a given ad group so that your ads are targeting the right audience.

Import Required Libraries¶

In [1]:
# Import 
import numpy as np
import pandas as pd
import altair as alt
import pandas_profiling as pp


import matplotlib.pyplot as plt
%matplotlib inline

import seaborn as sns

import warnings # to avoid warnings
warnings.filterwarnings('ignore')

/home/jeffwa/anaconda3/envs/DL_new/lib/python3.10/site-packages/tqdm/auto.py:22: TqdmWarning: IProgress not found. Please update jupyter and ipywidgets. See https://ipywidgets.readthedocs.io/en/stable/user_install.html
  from .autonotebook import tqdm as notebook_tqdm
/tmp/ipykernel_11672/160099804.py:5: DeprecationWarning: `import pandas_profiling` is going to be deprecated by April 1st. Please use `import ydata_profiling` instead.
  import pandas_profiling as pp
In [2]:
import plotly.graph_objs as go
import plotly.offline as pyo
import plotly.express as px
In [3]:
# display all columns of the dataframe
pd.options.display.max_columns = None

# use below code to convert the 'exponential' values to float
np.set_printoptions(suppress=True)
In [4]:
# set the plot size using 'rcParams'
# once the plot size is set using 'rcParams', it sets the size of all the forthcoming plots in the file
# pass width and height in inches to 'figure.figsize' 
plt.rcParams['figure.figsize'] = [15,8]

Load and Exploring the dataset¶

In [5]:
# load
df=pd.read_csv("dataset.csv",low_memory=False)

# preview 5 first 5 rows
df.head(5)
Out[5]:
campaign_item_id no_of_days time ext_service_id ext_service_name creative_id creative_width creative_height search_tags template_id landing_page advertiser_id advertiser_name network_id approved_budget advertiser_currency channel_id channel_name max_bid_cpm network_margin campaign_budget_usd impressions clicks stats_currency currency_code exchange_rate media_cost_usd position_in_content unique_reach total_reach search_tag_cat cmi_currency_code timezone weekday_cat keywords
0 2733 7 2022-05-01 128 Facebook Ads 1000 300.0 250.0 #The Power of X 90.0 https://www.abcjewelry.com/collections/boho-je... 4756 Web 190 400.0 SGD 32 Mobile NaN 0.0 652.173913 837 8 SGD SGD 1 14.058514 NaN NaN NaN Others SGD Asia/Singapore week_end delicate bracelets
1 2733 8 2022-05-02 16 DV360 1000 300.0 250.0 #Be Bold. Be X 90.0 https://www.abcjewelry.com/women/costume-jewelry/ 4756 Web 190 400.0 SGD 8 Social NaN 0.0 652.173913 2634 44 SGD SGD 1 99.633496 NaN NaN NaN Others SGD Asia/Singapore week_day summer jewelry
2 2733 9 2022-05-03 128 Facebook Ads 1000 300.0 250.0 #Embrace Your Individuality with X 90.0 https://www.abcjewelry.com/collections/boho-je... 4756 Web 190 400.0 SGD 8 Social NaN 0.0 652.173913 2135 32 SGD SGD 1 109.419677 NaN NaN NaN Others SGD Asia/Singapore week_day artisan jewelry
3 2733 10 2022-05-04 128 Facebook Ads 1000 300.0 250.0 #Be Bold. Be X 90.0 https://www.abcjewelry.com/collections/rhinest... 4756 Web 190 400.0 SGD 64 Video NaN 0.0 652.173913 2327 48 SGD SGD 1 115.209499 NaN NaN NaN Others SGD Asia/Singapore week_day layered bracelets
4 2733 11 2022-05-05 4 Google Ads 1000 300.0 250.0 #Be Bold. Be X 90.0 https://www.abcjewelry.com/collections/boho-je... 4756 Web 190 400.0 SGD 32 Mobile NaN 0.0 652.173913 1538 20 SGD SGD 1 66.990104 NaN NaN NaN Others SGD Asia/Singapore week_day minimalist jewelry
In [6]:
# see total number of rows
df.shape
Out[6]:
(72612, 35)
In [7]:
df.columns
Out[7]:
Index(['campaign_item_id', 'no_of_days', 'time', 'ext_service_id',
       'ext_service_name', 'creative_id', 'creative_width', 'creative_height',
       'search_tags', 'template_id', 'landing_page', 'advertiser_id',
       'advertiser_name', 'network_id', 'approved_budget',
       'advertiser_currency', 'channel_id', 'channel_name', 'max_bid_cpm',
       'network_margin', 'campaign_budget_usd', 'impressions', 'clicks',
       'stats_currency', 'currency_code', 'exchange_rate', 'media_cost_usd',
       'position_in_content', 'unique_reach', 'total_reach', 'search_tag_cat',
       'cmi_currency_code', 'timezone', 'weekday_cat', 'keywords'],
      dtype='object')
In [8]:
df.info() 

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 72612 entries, 0 to 72611
Data columns (total 35 columns):
 #   Column               Non-Null Count  Dtype  
---  ------               --------------  -----  
 0   campaign_item_id     72612 non-null  int64  
 1   no_of_days           72612 non-null  int64  
 2   time                 72612 non-null  object 
 3   ext_service_id       72612 non-null  int64  
 4   ext_service_name     72612 non-null  object 
 5   creative_id          72612 non-null  int64  
 6   creative_width       69200 non-null  float64
 7   creative_height      69200 non-null  float64
 8   search_tags          72612 non-null  object 
 9   template_id          69200 non-null  float64
 10  landing_page         72612 non-null  object 
 11  advertiser_id        72612 non-null  int64  
 12  advertiser_name      72612 non-null  object 
 13  network_id           72612 non-null  int64  
 14  approved_budget      72206 non-null  float64
 15  advertiser_currency  72612 non-null  object 
 16  channel_id           72612 non-null  int64  
 17  channel_name         72612 non-null  object 
 18  max_bid_cpm          7406 non-null   float64
 19  network_margin       72612 non-null  float64
 20  campaign_budget_usd  72612 non-null  float64
 21  impressions          72612 non-null  int64  
 22  clicks               72612 non-null  int64  
 23  stats_currency       72612 non-null  object 
 24  currency_code        72612 non-null  object 
 25  exchange_rate        72612 non-null  int64  
 26  media_cost_usd       72612 non-null  float64
 27  position_in_content  0 non-null      float64
 28  unique_reach         0 non-null      float64
 29  total_reach          0 non-null      float64
 30  search_tag_cat       72612 non-null  object 
 31  cmi_currency_code    72612 non-null  object 
 32  timezone             72612 non-null  object 
 33  weekday_cat          72612 non-null  object 
 34  keywords             72612 non-null  object 
dtypes: float64(11), int64(10), object(14)
memory usage: 19.4+ MB

Cleaning Null Values¶

In [9]:
# sort the variables on the basis of total null values in the variable
Total = df.isnull().sum().sort_values(ascending = False)          

#calculate nulls
Percent = (df.isnull().sum()*100/df.isnull().count()).sort_values(ascending = False)   
missing_data = pd.concat([Total, Percent], axis = 1, keys = ['Total', 'Percentage of Missing Values'])    

# add the column containing data type of each variable
missing_data['Type'] = df[missing_data.index].dtypes
missing_data
Out[9]:
Total Percentage of Missing Values Type
unique_reach 72612 100.000000 float64
total_reach 72612 100.000000 float64
position_in_content 72612 100.000000 float64
max_bid_cpm 65206 89.800584 float64
creative_width 3412 4.698948 float64
creative_height 3412 4.698948 float64
template_id 3412 4.698948 float64
approved_budget 406 0.559136 float64
exchange_rate 0 0.000000 int64
clicks 0 0.000000 int64
stats_currency 0 0.000000 object
currency_code 0 0.000000 object
campaign_item_id 0 0.000000 int64
media_cost_usd 0 0.000000 float64
campaign_budget_usd 0 0.000000 float64
search_tag_cat 0 0.000000 object
cmi_currency_code 0 0.000000 object
timezone 0 0.000000 object
weekday_cat 0 0.000000 object
impressions 0 0.000000 int64
channel_name 0 0.000000 object
network_margin 0 0.000000 float64
no_of_days 0 0.000000 int64
channel_id 0 0.000000 int64
advertiser_currency 0 0.000000 object
network_id 0 0.000000 int64
advertiser_name 0 0.000000 object
advertiser_id 0 0.000000 int64
landing_page 0 0.000000 object
search_tags 0 0.000000 object
creative_id 0 0.000000 int64
ext_service_name 0 0.000000 object
ext_service_id 0 0.000000 int64
time 0 0.000000 object
keywords 0 0.000000 object
In [10]:
# creative width
df['creative_width'] = df['creative_width'].fillna(0) 

# creative height
df['creative_height'] = df['creative_height'].fillna(0)

# template id
df['template_id'] = df['template_id'].fillna(-1)

# approved_budget
df['approved_budget'] = df['approved_budget'].fillna(0)

Drop unnecessary columns¶

  • Prune features that are entirely made up of null or actively harmful to analysis.
In [11]:
df.drop(columns=['position_in_content','unique_reach','total_reach','max_bid_cpm'],inplace=True)
  • no_of_days : campaigns run for atleast a month , so when no_of_days == 0 means one day only.
In [12]:
df.describe()
Out[12]:
campaign_item_id no_of_days ext_service_id creative_id creative_width creative_height template_id advertiser_id network_id approved_budget channel_id network_margin campaign_budget_usd impressions clicks exchange_rate media_cost_usd
count 72612.000000 72612.000000 72612.000000 72612.000000 72612.000000 72612.000000 72612.000000 72612.000000 72612.000000 7.261200e+04 72612.000000 72612.0 72612.000000 72612.000000 72612.000000 72612.0 72612.000000
mean 3130.143282 27.036344 49.449127 7450.124842 255.226409 212.688674 79.131659 6195.862213 345.272861 1.251030e+05 21.847780 0.0 617.630284 1370.161847 52.696386 1.0 11.819999
std 142.154918 25.479175 55.881324 4062.384982 106.899767 89.083139 27.033401 387.864576 23.707191 5.611237e+05 23.759286 0.0 1354.606619 3702.699962 377.308103 0.0 48.370659
min 2733.000000 0.000000 4.000000 1000.000000 0.000000 0.000000 -1.000000 4756.000000 188.000000 0.000000e+00 1.000000 0.0 2.452316 511.000000 2.000000 1.0 0.000000
25% 3148.000000 9.000000 4.000000 3725.000000 300.000000 250.000000 90.000000 6319.000000 353.000000 6.000000e+03 4.000000 0.0 205.994550 526.000000 5.000000 1.0 0.435791
50% 3173.000000 19.000000 16.000000 7855.000000 300.000000 250.000000 90.000000 6385.000000 353.000000 1.000000e+04 8.000000 0.0 377.656676 577.000000 8.000000 1.0 1.709001
75% 3202.000000 37.000000 128.000000 10995.000000 300.000000 250.000000 90.000000 6394.000000 353.000000 1.500000e+04 32.000000 0.0 755.313351 816.000000 13.000000 1.0 8.276369
max 3960.000000 118.000000 128.000000 15605.000000 300.000000 250.000000 93.000000 6490.000000 353.000000 6.000000e+06 64.000000 0.0 39559.896155 153959.000000 31807.000000 1.0 2295.028945
  • ext_service_name : most ads were Facebook Ads since it is the most populated social channel for target audience.
  • landing_page : boho jewelry page has the most clicked ads.
In [13]:
# summary of categorical variables
df.describe(include=object)

# Note: If we pass 'include=object' to the .describe(), it will return descriptive statistics for categorical variables only
Out[13]:
time ext_service_name search_tags landing_page advertiser_name advertiser_currency channel_name stats_currency currency_code search_tag_cat cmi_currency_code timezone weekday_cat keywords
count 72612 72612 72612 72612 72612 72612 72612 72612 72612 72612 72612 72612 72612 72612
unique 224 3 6 45 44 5 5 5 5 5 5 7 2 118
top 2022-10-22 Facebook Ads #The Ultimate Fashion Statement with X https://www.abcjewelry.com/collections/boho-je... Oman AED Mobile AED AED Others AED Asia/Kolkata week_day beaded jewelry
freq 955 24275 12293 1684 8641 53661 14625 53661 53661 49008 53661 64264 62424 674

Creating a metric to measure Clicks per appearance¶

In [14]:
df['ctr']=(df['clicks']/df['impressions'])*100
In [15]:
df.loc[:,:].sort_values('ctr',ascending=False)[1000:1050]
Out[15]:
campaign_item_id no_of_days time ext_service_id ext_service_name creative_id creative_width creative_height search_tags template_id landing_page advertiser_id advertiser_name network_id approved_budget advertiser_currency channel_id channel_name network_margin campaign_budget_usd impressions clicks stats_currency currency_code exchange_rate media_cost_usd search_tag_cat cmi_currency_code timezone weekday_cat keywords ctr
16117 2977 2 2022-08-27 16 DV360 3145 300.0 250.0 #The Power of X 90.0 https://www.abcjewelry.com/collections 6112 Coimbatore 353 15000.0 INR 64 Video 0.0 247.005064 1504 98 INR INR 1 4.650439 Others INR Asia/Kolkata week_day body jewelry 6.515957
68678 3543 34 2022-05-19 4 Google Ads 13525 300.0 250.0 #Embrace Your Individuality with X 90.0 https://www.abcjewelry.com/collections/chunky-... 6370 North 353 5000.0 INR 8 Social 0.0 9812.339016 13923 907 INR INR 1 174.517128 Others INR Asia/Kolkata week_day dazzling jewelry 6.514401
21303 3128 10 2022-09-02 4 Google Ads 4625 300.0 250.0 #The Power of X 93.0 https://www.abcjewelry.com/women/bold-jewelry/ 6331 Tamil Nadu 353 870000.0 INR 4 Search 0.0 441.982072 5965 388 INR INR 1 66.840845 Retargeting INR Asia/Kolkata week_day renaissance jewelry 6.504610
70357 3055 24 2022-10-25 4 Google Ads 14265 0.0 0.0 #The Ultimate Fashion Statement with X -1.0 https://www.abcjewelry.com/collections/stateme... 6331 Tamil Nadu 353 268786.0 INR 4 Search 0.0 11827.689243 24953 1622 INR INR 1 360.303654 Others INR Asia/Kolkata week_day cuff bracelets 6.500220
21196 3128 24 2022-09-11 128 Facebook Ads 4590 300.0 250.0 #Embrace Your Individuality with X 90.0 https://www.abcjewelry.com/collections/costume... 6331 Tamil Nadu 353 870000.0 INR 64 Video 0.0 2241.035857 6155 400 INR INR 1 74.365122 Others INR Asia/Kolkata week_end layered bracelets 6.498781
15871 2949 17 2022-10-11 4 Google Ads 3070 300.0 250.0 #The Power of X 90.0 https://www.abcjewelry.com/collections/mixed-m... 5908 Addis Ababa 353 160000.0 INR 32 Mobile 0.0 982.921735 1139 74 INR INR 1 7.205023 Others INR Asia/Kolkata week_day dangle earrings 6.496927
72476 3533 3 2022-11-20 4 Google Ads 15570 0.0 0.0 #The X Factor - Fashion for the Fearless -1.0 https://www.abcjewelry.com/women/fashion-jewelry/ 6307 Punjab 353 163135.0 INR 4 Search 0.0 306.635594 832 54 INR INR 1 3.380317 Others INR Asia/Kolkata week_end body chains 6.490385
18930 3051 38 2022-09-25 128 Facebook Ads 3945 300.0 250.0 #The Ultimate Fashion Statement with X 90.0 https://www.abcjewelry.com/collections/mixed-m... 5641 Karnataka 353 5000.0 INR 64 Video 0.0 1792.828685 4623 300 INR INR 1 72.674296 Retargeting INR Asia/Kolkata week_end zodiac jewelry 6.489293
19279 3054 32 2022-08-23 128 Facebook Ads 4045 300.0 250.0 #The Power of X 90.0 https://www.abcjewelry.com/collections/afforda... 5641 Karnataka 353 5000.0 INR 64 Video 0.0 1677.041833 4207 273 INR INR 1 32.624534 Others INR Asia/Kolkata week_day handcrafted jewelry 6.489185
20248 3103 12 2022-10-10 4 Google Ads 4330 300.0 250.0 #Timeless X Style 90.0 https://www.abcjewelry.com/collections/beaded-... 6370 North 353 100000.0 INR 8 Social 0.0 3090.616887 11698 759 INR INR 1 118.416547 Others INR Asia/Kolkata week_day bridal jewelry 6.488289
70343 3055 10 2022-10-11 4 Google Ads 14265 0.0 0.0 #The X Factor - Fashion for the Fearless -1.0 https://www.abcjewelry.com/collections/stateme... 6331 Tamil Nadu 353 268786.0 INR 4 Search 0.0 11827.689243 34263 2222 INR INR 1 497.550045 Others INR Asia/Kolkata week_day whimsical jewelry 6.485130
69691 3049 25 2022-10-25 16 DV360 13965 0.0 0.0 #Timeless X Style -1.0 https://www.abcjewelry.com/collections/chunky-... 6298 Gujarat 353 0.0 INR 32 Mobile 0.0 8715.139442 18027 1169 INR INR 1 263.858285 Others INR Asia/Kolkata week_day birthstone jewelry 6.484717
7412 2802 63 2022-09-01 16 DV360 1740 0.0 0.0 #Timeless X Style 23.0 https://www.abcjewelry.com/collections/rhinest... 5395 Luxor 191 500000.0 EGP 4 Search 0.0 18.093487 4426 287 EGP EGP 1 18.383460 Others EGP Africa/Cairo week_day layered earrings 6.484410
18729 3048 14 2022-10-12 16 DV360 3890 300.0 250.0 #The Power of X 90.0 https://www.abcjewelry.com/collections/cuff-br... 6298 Gujarat 353 0.0 INR 32 Mobile 0.0 1867.529880 5815 377 INR INR 1 71.890183 Others INR Asia/Kolkata week_day mixed metal jewelry 6.483233
70867 3104 18 2022-10-19 128 Facebook Ads 14455 0.0 0.0 #The Ultimate Fashion Statement with X -1.0 https://www.abcjewelry.com/women/costume-jewelry/ 6370 North 353 100000.0 INR 8 Social 0.0 39559.896155 98968 6416 INR INR 1 1325.016718 Others INR Asia/Kolkata week_day artisanal jewelry 6.482904
70342 3055 9 2022-10-10 128 Facebook Ads 14265 0.0 0.0 #Be Bold. Be X -1.0 https://www.abcjewelry.com/collections/unique-... 6331 Tamil Nadu 353 268786.0 INR 4 Search 0.0 11827.689243 16806 1089 INR INR 1 240.213963 Others INR Asia/Kolkata week_day layered jewelry 6.479829
19412 3054 8 2022-09-14 4 Google Ads 4085 300.0 250.0 #Embrace Your Individuality with X 90.0 https://www.abcjewelry.com/women/handmade-jewe... 5641 Karnataka 353 5000.0 INR 32 Mobile 0.0 622.509960 5525 358 INR INR 1 31.954743 Others INR Asia/Kolkata week_day chunky jewelry 6.479638
69711 3049 18 2022-10-19 128 Facebook Ads 13970 0.0 0.0 #Embrace Your Individuality with X -1.0 https://www.abcjewelry.com/women/statement-jew... 6298 Gujarat 353 0.0 INR 32 Mobile 0.0 622.509960 2732 177 INR INR 1 26.514287 Others INR Asia/Kolkata week_day multi-strand necklaces 6.478770
19351 3054 31 2022-09-18 128 Facebook Ads 4065 300.0 250.0 #The Ultimate Fashion Statement with X 90.0 https://www.abcjewelry.com/women/designer-insp... 5641 Karnataka 353 5000.0 INR 8 Social 0.0 2241.035857 8584 556 INR INR 1 75.919705 Others INR Asia/Kolkata week_end art nouveau jewelry 6.477167
69685 3049 19 2022-10-19 16 DV360 13965 0.0 0.0 #The Power of X -1.0 https://www.abcjewelry.com/women/statement-jew... 6298 Gujarat 353 0.0 INR 64 Video 0.0 8715.139442 19256 1247 INR INR 1 282.374345 Others INR Asia/Kolkata week_day pearl jewelry 6.475904
7396 2802 46 2022-08-15 16 DV360 1740 0.0 0.0 #The X Factor - Fashion for the Fearless 23.0 https://www.abcjewelry.com/women/handmade-jewe... 5395 Luxor 191 125000.0 EGP 8 Social 0.0 18.093487 4126 267 EGP EGP 1 17.582524 Others EGP Africa/Cairo week_day rhinestone jewelry 6.471159
69705 3049 12 2022-10-13 4 Google Ads 13970 0.0 0.0 #The Ultimate Fashion Statement with X -1.0 https://www.abcjewelry.com/women/unique-jewelry/ 6298 Gujarat 353 0.0 INR 8 Social 0.0 622.509960 2658 172 INR INR 1 26.521432 Others INR Asia/Kolkata week_day mixed metal jewelry 6.471031
18976 3051 6 2022-09-11 4 Google Ads 3965 300.0 250.0 #Be Bold. Be X 93.0 https://www.abcjewelry.com/collections/stateme... 5641 Karnataka 353 5000.0 INR 1 Display 0.0 498.007968 3091 200 INR INR 1 24.771378 Retargeting INR Asia/Kolkata week_end chic and affordable jewelry 6.470398
17198 3050 14 2022-09-07 16 DV360 3465 300.0 250.0 #The Power of X 90.0 https://www.abcjewelry.com/collections/gemston... 6337 Kuala Lumpur 353 268000.0 INR 64 Video 0.0 4980.079681 79861 5166 INR INR 1 235.481599 Others INR Asia/Kolkata week_day statement rings 6.468739
19621 3058 9 2022-10-07 4 Google Ads 4135 300.0 250.0 #The X Factor - Fashion for the Fearless 90.0 https://www.abcjewelry.com/collections/mixed-m... 6319 Orissa 353 215000.0 INR 8 Social 0.0 1494.023904 5148 333 INR INR 1 57.216612 Others INR Asia/Kolkata week_day art nouveau jewelry 6.468531
22368 3134 21 2022-09-08 16 DV360 4940 300.0 250.0 #The X Factor - Fashion for the Fearless 90.0 https://www.abcjewelry.com/collections/stateme... 6319 Orissa 353 415000.0 INR 8 Social 0.0 1245.019920 5442 352 INR INR 1 39.140953 Others INR Asia/Kolkata week_day beaded bracelets 6.468210
69678 3049 12 2022-10-12 128 Facebook Ads 13965 0.0 0.0 #Embrace Your Individuality with X -1.0 https://www.abcjewelry.com/collections/tassel-... 6298 Gujarat 353 0.0 INR 4 Search 0.0 8715.139442 24480 1583 INR INR 1 361.062937 Others INR Asia/Kolkata week_day hair accessories 6.466503
69725 3049 6 2022-10-27 4 Google Ads 13975 0.0 0.0 #Timeless X Style -1.0 https://www.abcjewelry.com/collections/beaded-... 6298 Gujarat 353 0.0 INR 1 Display 0.0 622.509960 8695 562 INR INR 1 43.544909 Others INR Asia/Kolkata week_day initial jewelry 6.463485
16532 3035 8 2022-10-06 16 DV360 3285 300.0 250.0 #The X Factor - Fashion for the Fearless 90.0 https://www.abcjewelry.com/collections/stateme... 6292 Andhra Pradesh 353 205000.0 INR 32 Mobile 0.0 2490.039841 16863 1089 INR INR 1 275.329567 Others INR Asia/Kolkata week_day luxury jewelry 6.457926
7411 2802 62 2022-08-31 128 Facebook Ads 1740 0.0 0.0 #Timeless X Style 23.0 https://www.abcjewelry.com/collections/dainty-... 5395 Luxor 191 500000.0 EGP 64 Video 0.0 18.093487 3887 251 EGP EGP 1 17.277772 Others EGP Africa/Cairo week_day rhinestone jewelry 6.457422
19150 3054 15 2022-08-24 128 Facebook Ads 4010 300.0 250.0 #Timeless X Style 93.0 https://www.abcjewelry.com/collections/boho-je... 5641 Karnataka 353 5000.0 INR 4 Search 0.0 249.003984 2323 150 INR INR 1 17.683272 Retargeting INR Asia/Kolkata week_day dangle earrings 6.457167
70340 3055 7 2022-10-08 16 DV360 14265 0.0 0.0 #The Ultimate Fashion Statement with X -1.0 https://www.abcjewelry.com/collections/unique-... 6331 Tamil Nadu 353 268786.0 INR 32 Mobile 0.0 11827.689243 25988 1678 INR INR 1 375.560319 Others INR Asia/Kolkata week_day unique jewelry 6.456826
18275 3046 10 2022-09-02 16 DV360 3765 300.0 250.0 #The Power of X 93.0 https://www.abcjewelry.com/women/fashion-jewelry 6298 Gujarat 353 0.0 INR 4 Search 0.0 367.280876 6134 396 INR INR 1 57.184589 Retargeting INR Asia/Kolkata week_day body jewelry 6.455820
20849 3130 11 2022-09-04 4 Google Ads 4490 300.0 250.0 #The Ultimate Fashion Statement with X 93.0 https://www.abcjewelry.com/women/designer-insp... 6334 Thailand 353 1010000.0 INR 64 Video 0.0 423.306773 6739 435 INR INR 1 63.892121 Retargeting INR Asia/Kolkata week_end drop earrings 6.454964
16531 3035 7 2022-10-05 4 Google Ads 3285 300.0 250.0 #The Power of X 90.0 https://www.abcjewelry.com/collections/trendy-... 6292 Andhra Pradesh 353 205000.0 INR 32 Mobile 0.0 2490.039841 15959 1030 INR INR 1 275.793563 Others INR Asia/Kolkata week_day baroque jewelry 6.454038
7390 2802 40 2022-08-09 128 Facebook Ads 1740 0.0 0.0 #Embrace Your Individuality with X 23.0 https://www.abcjewelry.com/collections/beaded-... 5395 Luxor 191 125000.0 EGP 64 Video 0.0 18.093487 4649 300 EGP EGP 1 18.996798 Others EGP Africa/Cairo week_day tribal jewelry 6.453001
70351 3055 18 2022-10-19 16 DV360 14265 0.0 0.0 #The Ultimate Fashion Statement with X -1.0 https://www.abcjewelry.com/collections 6331 Tamil Nadu 353 268786.0 INR 8 Social 0.0 11827.689243 26750 1724 INR INR 1 386.790570 Others INR Asia/Kolkata week_day chunky jewelry 6.444860
69673 3049 7 2022-10-07 4 Google Ads 13965 0.0 0.0 #Timeless X Style -1.0 https://www.abcjewelry.com/women/bold-jewelry/ 6298 Gujarat 353 0.0 INR 32 Mobile 0.0 8715.139442 21785 1403 INR INR 1 320.468509 Others INR Asia/Kolkata week_day victorian jewelry 6.440211
71122 3128 0 2022-08-18 4 Google Ads 14560 0.0 0.0 #The Power of X -1.0 https://www.abcjewelry.com/collections/unique-... 6331 Tamil Nadu 353 870000.0 INR 1 Display 0.0 1792.828685 2907 187 INR INR 1 30.092506 Others INR Asia/Kolkata week_day formal jewelry 6.432749
69822 3050 21 2022-10-22 128 Facebook Ads 14010 0.0 0.0 #The Ultimate Fashion Statement with X -1.0 https://www.abcjewelry.com/women/trendy-jewelry/ 5641 Karnataka 353 805000.0 INR 32 Mobile 0.0 22410.358566 38101 2449 INR INR 1 670.896561 Others INR Asia/Kolkata week_day multi-strand necklaces 6.427653
70415 3056 23 2022-10-24 16 DV360 14280 0.0 0.0 #The Ultimate Fashion Statement with X -1.0 https://www.abcjewelry.com/collections/layered... 5686 Maharashtra 353 1015000.0 INR 8 Social 0.0 622.509960 2552 164 INR INR 1 27.027827 Others INR Asia/Kolkata week_day formal jewelry 6.426332
16540 3035 16 2022-10-14 16 DV360 3285 300.0 250.0 #The Ultimate Fashion Statement with X 90.0 https://www.abcjewelry.com/collections/vintage... 6292 Andhra Pradesh 353 205000.0 INR 32 Mobile 0.0 2490.039841 8342 536 INR INR 1 97.199157 Others INR Asia/Kolkata week_day dazzling jewelry 6.425318
15796 2949 14 2022-11-10 128 Facebook Ads 3045 300.0 250.0 #Embrace Your Individuality with X 90.0 https://www.abcjewelry.com/collections/minimal... 5908 Addis Ababa 353 1000.0 INR 8 Social 0.0 1228.652169 3145 202 INR INR 1 31.389407 Others INR Asia/Kolkata week_day trendy jewelry 6.422893
15890 2949 19 2022-10-13 16 DV360 3075 300.0 250.0 #Embrace Your Individuality with X 90.0 https://www.abcjewelry.com/women/designer-insp... 5908 Addis Ababa 353 160000.0 INR 8 Social 0.0 982.921735 2274 146 INR INR 1 22.230288 Others INR Asia/Kolkata week_day handmade jewelry 6.420405
70411 3056 19 2022-10-20 128 Facebook Ads 14280 0.0 0.0 #Be Bold. Be X -1.0 https://www.abcjewelry.com/women/designer-insp... 5686 Maharashtra 353 1015000.0 INR 8 Social 0.0 622.509960 2430 156 INR INR 1 25.013629 Others INR Asia/Kolkata week_day unique and trendy jewelry 6.419753
19631 3058 19 2022-10-17 4 Google Ads 4135 300.0 250.0 #The Power of X 90.0 https://www.abcjewelry.com/collections/beaded-... 6319 Orissa 353 215000.0 INR 4 Search 0.0 1494.023904 5500 353 INR INR 1 57.680075 Others INR Asia/Kolkata week_day huggie earrings 6.418182
18727 3048 12 2022-10-10 4 Google Ads 3890 300.0 250.0 #Timeless X Style 90.0 https://www.abcjewelry.com/collections/minimal... 6298 Gujarat 353 0.0 INR 4 Search 0.0 1867.529880 8656 555 INR INR 1 71.521836 Others INR Asia/Kolkata week_day renaissance jewelry 6.411738
70356 3055 23 2022-10-24 16 DV360 14265 0.0 0.0 #The Power of X -1.0 https://www.abcjewelry.com/women/bold-jewelry/ 6331 Tamil Nadu 353 268786.0 INR 4 Search 0.0 11827.689243 25623 1640 INR INR 1 370.182789 Others INR Asia/Kolkata week_day affordable jewelry 6.400500
70350 3055 17 2022-10-18 128 Facebook Ads 14265 0.0 0.0 #Be Bold. Be X -1.0 https://www.abcjewelry.com/collections/bold-je... 6331 Tamil Nadu 353 268786.0 INR 4 Search 0.0 11827.689243 26723 1710 INR INR 1 386.405183 Others INR Asia/Kolkata week_day victorian jewelry 6.398982
19215 3054 13 2022-09-05 16 DV360 4030 300.0 250.0 #The X Factor - Fashion for the Fearless 93.0 https://www.abcjewelry.com/women/boho-jewelry/ 5641 Karnataka 353 5000.0 INR 64 Video 0.0 560.258964 8189 524 INR INR 1 74.654203 Others INR Asia/Kolkata week_day vintage-inspired jewelry 6.398828
In [16]:
df.loc[:,:].sort_values('clicks',ascending=False)[1000:1050]
Out[16]:
campaign_item_id no_of_days time ext_service_id ext_service_name creative_id creative_width creative_height search_tags template_id landing_page advertiser_id advertiser_name network_id approved_budget advertiser_currency channel_id channel_name network_margin campaign_budget_usd impressions clicks stats_currency currency_code exchange_rate media_cost_usd search_tag_cat cmi_currency_code timezone weekday_cat keywords ctr
17346 3052 38 2022-08-29 16 DV360 3510 300.0 250.0 #The X Factor - Fashion for the Fearless 90.0 https://www.abcjewelry.com/women/handmade-jewe... 6337 Kuala Lumpur 353 1015000.0 INR 4 Search 0.0 1613.545817 4051 528 INR INR 1 42.094553 Others INR Asia/Kolkata week_day dangle earrings 13.033819
16242 2980 12 2022-08-20 128 Facebook Ads 3200 300.0 250.0 #Be Bold. Be X 90.0 https://www.abcjewelry.com/women/minimalist-je... 5755 Malad 353 10000.0 INR 8 Social 0.0 4322.588613 11080 528 INR INR 1 94.714367 Others INR Asia/Kolkata week_day spring jewelry 4.765343
16227 2980 29 2022-09-06 4 Google Ads 3195 300.0 250.0 #The X Factor - Fashion for the Fearless 90.0 https://www.abcjewelry.com/collections/bold-je... 5755 Malad 353 10000.0 INR 1 Display 0.0 4322.588613 6610 528 INR INR 1 49.312327 Others INR Asia/Kolkata week_day funky jewelry 7.987897
22371 3134 24 2022-09-11 16 DV360 4940 300.0 250.0 #The X Factor - Fashion for the Fearless 90.0 https://www.abcjewelry.com/women/costume-jewelry/ 6319 Orissa 353 415000.0 INR 4 Search 0.0 1245.019920 4095 528 INR INR 1 39.160444 Others INR Asia/Kolkata week_end statement necklaces 12.893773
20349 3107 9 2022-10-07 16 DV360 4350 300.0 250.0 #Embrace Your Individuality with X 90.0 https://www.abcjewelry.com/women/boho-jewelry/ 6334 Thailand 353 295000.0 INR 1 Display 0.0 3735.059761 9829 528 INR INR 1 143.196029 Others INR Asia/Kolkata week_day delicate bracelets 5.371859
20566 3129 16 2022-08-21 4 Google Ads 4415 300.0 250.0 #Embrace Your Individuality with X 90.0 https://www.abcjewelry.com/collections/vintage... 6334 Thailand 353 1010000.0 INR 1 Display 0.0 2056.772908 11731 527 INR INR 1 96.560533 Others INR Asia/Kolkata week_end religious jewelry 4.492371
70476 3058 23 2022-10-23 16 DV360 14310 0.0 0.0 #Be Bold. Be X -1.0 https://www.abcjewelry.com/collections 6328 Madhya Pradesh 353 115000.0 INR 1 Display 0.0 3735.059761 10506 527 INR INR 1 117.851509 Others INR Asia/Kolkata week_end cuff bracelets 5.016181
19614 3058 2 2022-09-30 4 Google Ads 4135 300.0 250.0 #Timeless X Style 90.0 https://www.abcjewelry.com/collections/dainty-... 6319 Orissa 353 215000.0 INR 4 Search 0.0 1494.023904 7646 526 INR INR 1 74.726546 Others INR Asia/Kolkata week_day hoop earrings 6.879414
22480 3134 5 2022-08-30 128 Facebook Ads 4980 300.0 250.0 #Timeless X Style 90.0 https://www.abcjewelry.com/collections/vintage... 6319 Orissa 353 415000.0 INR 1 Display 0.0 1359.561753 13387 526 INR INR 1 102.737796 Others INR Asia/Kolkata week_day concert jewelry 3.929185
17746 3053 12 2022-09-06 4 Google Ads 3625 300.0 250.0 #The Power of X 90.0 https://www.abcjewelry.com/women/handmade-jewe... 6337 Kuala Lumpur 353 1015000.0 INR 32 Mobile 0.0 1222.609562 15259 526 INR INR 1 84.628406 Others INR Asia/Kolkata week_day handcrafted jewelry 3.447146
72578 3563 16 2022-12-03 128 Facebook Ads 15595 0.0 0.0 #Be Bold. Be X -1.0 https://www.abcjewelry.com/collections/stateme... 6334 Thailand 353 1905942.0 INR 8 Social 0.0 2085.122041 10115 525 INR INR 1 88.484745 Others INR Asia/Kolkata week_day body piercings 5.190311
21350 3128 3 2022-08-28 16 DV360 4645 300.0 250.0 #The Power of X 90.0 https://www.abcjewelry.com/collections/layered... 6331 Tamil Nadu 353 870000.0 INR 1 Display 0.0 1973.356574 19197 525 INR INR 1 150.480855 Others INR Asia/Kolkata week_end vibrant jewelry 2.734802
16535 3035 11 2022-10-09 16 DV360 3285 300.0 250.0 #The X Factor - Fashion for the Fearless 90.0 https://www.abcjewelry.com/collections/unique-... 6292 Andhra Pradesh 353 205000.0 INR 1 Display 0.0 2490.039841 7874 524 INR INR 1 96.970581 Others INR Asia/Kolkata week_end statement pins 6.654813
16539 3035 15 2022-10-13 16 DV360 3285 300.0 250.0 #Be Bold. Be X 90.0 https://www.abcjewelry.com/collections/beaded-... 6292 Andhra Pradesh 353 205000.0 INR 8 Social 0.0 2490.039841 9067 524 INR INR 1 97.144440 Others INR Asia/Kolkata week_day layered bracelets 5.779199
19215 3054 13 2022-09-05 16 DV360 4030 300.0 250.0 #The X Factor - Fashion for the Fearless 93.0 https://www.abcjewelry.com/women/boho-jewelry/ 5641 Karnataka 353 5000.0 INR 64 Video 0.0 560.258964 8189 524 INR INR 1 74.654203 Others INR Asia/Kolkata week_day vintage-inspired jewelry 6.398828
70618 3099 19 2022-08-24 4 Google Ads 14350 0.0 0.0 #Embrace Your Individuality with X -1.0 https://www.abcjewelry.com/women/minimalist-je... 6370 North 353 100000.0 INR 8 Social 0.0 1844.480158 14877 523 INR INR 1 98.331425 Others INR Asia/Kolkata week_day affordable statement jewelry 3.515494
20611 3129 21 2022-08-26 128 Facebook Ads 4425 300.0 250.0 #Be Bold. Be X 90.0 https://www.abcjewelry.com/collections/dainty-... 6334 Thailand 353 1010000.0 INR 64 Video 0.0 2025.560259 13329 522 INR INR 1 120.078141 Others INR Asia/Kolkata week_day winter jewelry 3.916273
19710 3099 2 2022-07-06 128 Facebook Ads 4160 300.0 250.0 #Embrace Your Individuality with X 90.0 https://www.abcjewelry.com/collections/pendant... 6370 North 353 100000.0 INR 8 Social 0.0 1854.370132 20199 522 INR INR 1 166.163153 Others INR Asia/Kolkata week_day zodiac jewelry 2.584286
19713 3099 5 2022-07-09 4 Google Ads 4160 300.0 250.0 #Timeless X Style 90.0 https://www.abcjewelry.com/collections/stateme... 6370 North 353 100000.0 INR 4 Search 0.0 1854.370132 18626 521 INR INR 1 166.078852 Others INR Asia/Kolkata week_day body jewelry 2.797165
17459 3052 24 2022-08-29 128 Facebook Ads 3535 300.0 250.0 #Be Bold. Be X 90.0 https://www.abcjewelry.com/collections/stateme... 6337 Kuala Lumpur 353 1015000.0 INR 4 Search 0.0 2000.747012 10987 521 INR INR 1 118.084605 Others INR Asia/Kolkata week_day victorian jewelry 4.741968
21104 3128 22 2022-08-27 128 Facebook Ads 4560 300.0 250.0 #Embrace Your Individuality with X 90.0 https://www.abcjewelry.com/women/designer-insp... 6331 Tamil Nadu 353 870000.0 INR 1 Display 0.0 2000.747012 14665 520 INR INR 1 115.597048 Others INR Asia/Kolkata week_day personalized jewelry 3.545857
22225 3133 17 2022-08-22 128 Facebook Ads 4885 300.0 250.0 #Embrace Your Individuality with X 90.0 https://www.abcjewelry.com/collections/bold-je... 6319 Orissa 353 415000.0 INR 8 Social 0.0 1146.663347 10153 520 INR INR 1 71.956222 Others INR Asia/Kolkata week_day beaded jewelry 5.121639
69626 3048 5 2022-10-05 4 Google Ads 13950 0.0 0.0 #Timeless X Style -1.0 https://www.abcjewelry.com/women/bold-jewelry/ 6325 Chandpur 353 85000.0 INR 32 Mobile 0.0 2490.039841 6645 520 INR INR 1 93.972719 Others INR Asia/Kolkata week_day victorian jewelry 7.825433
22175 3133 31 2022-08-26 16 DV360 4875 300.0 250.0 #Embrace Your Individuality with X 90.0 https://www.abcjewelry.com/collections/beaded-... 6319 Orissa 353 415000.0 INR 32 Mobile 0.0 1020.916335 10339 519 INR INR 1 61.046414 Others INR Asia/Kolkata week_day hair accessories 5.019828
15826 2949 14 2022-11-10 4 Google Ads 3055 300.0 250.0 #The Ultimate Fashion Statement with X 90.0 https://www.abcjewelry.com/collections/delicat... 5908 Addis Ababa 353 1000.0 INR 8 Social 0.0 1228.652169 4723 519 INR INR 1 30.282426 Others INR Asia/Kolkata week_day pearl jewelry 10.988778
72584 3563 22 2022-12-09 4 Google Ads 15595 0.0 0.0 #Timeless X Style -1.0 https://www.abcjewelry.com/collections/chunky-... 6334 Thailand 353 1905942.0 INR 32 Mobile 0.0 2085.122041 10296 519 INR INR 1 91.953161 Others INR Asia/Kolkata week_day layered jewelry 5.040793
20584 3130 27 2022-09-14 4 Google Ads 4420 300.0 250.0 #The Power of X 90.0 https://www.abcjewelry.com/collections/gemston... 6334 Thailand 353 1010000.0 INR 1 Display 0.0 2241.035857 12191 518 INR INR 1 74.802909 Others INR Asia/Kolkata week_day ear cuffs 4.249036
16310 2982 4 2022-08-16 4 Google Ads 3220 300.0 250.0 #Embrace Your Individuality with X 90.0 https://www.abcjewelry.com/women/minimalist-je... 6019 Indore 353 15000.0 INR 4 Search 0.0 2470.050636 14556 518 INR INR 1 90.729686 Others INR Asia/Kolkata week_day initial jewelry 3.558670
18731 3048 16 2022-10-14 4 Google Ads 3890 300.0 250.0 #The Power of X 90.0 https://www.abcjewelry.com/collections/minimal... 6298 Gujarat 353 0.0 INR 8 Social 0.0 1867.529880 7498 518 INR INR 1 71.824478 Others INR Asia/Kolkata week_day midi rings 6.908509
70391 3055 6 2022-10-27 128 Facebook Ads 14275 0.0 0.0 #Embrace Your Individuality with X -1.0 https://www.abcjewelry.com/women/vintage-inspi... 6331 Tamil Nadu 353 268786.0 INR 1 Display 0.0 1245.019920 9802 518 INR INR 1 74.573195 Others INR Asia/Kolkata week_day baroque jewelry 5.284636
70619 3099 20 2022-08-25 16 DV360 14350 0.0 0.0 #The Power of X -1.0 https://www.abcjewelry.com/collections/rhinest... 6370 North 353 100000.0 INR 4 Search 0.0 1844.480158 13638 518 INR INR 1 98.634677 Others INR Asia/Kolkata week_day art nouveau jewelry 3.798211
21536 3131 10 2022-07-14 4 Google Ads 4695 300.0 250.0 #The Power of X 90.0 https://www.abcjewelry.com/collections/trendy-... 5686 Maharashtra 353 1015000.0 INR 8 Social 0.0 3735.059761 26542 517 INR INR 1 270.690742 Others INR Asia/Kolkata week_day fashion jewelry for women 1.947856
71298 3130 10 2022-08-28 16 DV360 14680 0.0 0.0 #The Ultimate Fashion Statement with X -1.0 https://www.abcjewelry.com/collections/layered... 6334 Thailand 353 1010000.0 INR 32 Mobile 0.0 2241.035857 10895 517 INR INR 1 74.266140 Others INR Asia/Kolkata week_end bold jewelry 4.745296
21353 3128 6 2022-08-31 4 Google Ads 4645 300.0 250.0 #Be Bold. Be X 90.0 https://www.abcjewelry.com/collections/tassel-... 6331 Tamil Nadu 353 870000.0 INR 32 Mobile 0.0 1973.356574 18903 516 INR INR 1 148.106892 Others INR Asia/Kolkata week_day seasonal jewelry 2.729725
70315 3054 10 2022-08-28 16 DV360 14240 0.0 0.0 #The Ultimate Fashion Statement with X -1.0 https://www.abcjewelry.com/collections/mixed-m... 5641 Karnataka 353 5000.0 INR 64 Video 0.0 2241.035857 9340 515 INR INR 1 74.214514 Others INR Asia/Kolkata week_end unique jewelry 5.513919
20210 3103 16 2022-07-02 16 DV360 4320 300.0 250.0 #Timeless X Style 90.0 https://www.abcjewelry.com/collections/stateme... 6370 North 353 100000.0 INR 32 Mobile 0.0 3090.616887 15189 514 INR INR 1 105.631295 Others INR Asia/Kolkata week_day bangles 3.384028
16639 3039 20 2022-07-25 16 DV360 3310 300.0 250.0 #The X Factor - Fashion for the Fearless 90.0 https://www.abcjewelry.com/collections/pearl-j... 6292 Andhra Pradesh 353 905000.0 INR 32 Mobile 0.0 2490.039841 15922 514 INR INR 1 203.030094 Others INR Asia/Kolkata week_day beach jewelry 3.228238
70470 3058 17 2022-10-17 4 Google Ads 14310 0.0 0.0 #The Power of X -1.0 https://www.abcjewelry.com/collections/designe... 6328 Madhya Pradesh 353 115000.0 INR 1 Display 0.0 3735.059761 10992 514 INR INR 1 123.582821 Others INR Asia/Kolkata week_day beaded jewelry 4.676128
19406 3054 2 2022-09-08 4 Google Ads 4085 300.0 250.0 #Be Bold. Be X 90.0 https://www.abcjewelry.com/collections/hoop-ea... 5641 Karnataka 353 5000.0 INR 32 Mobile 0.0 622.509960 7057 514 INR INR 1 31.943065 Others INR Asia/Kolkata week_day art nouveau jewelry 7.283548
17976 3056 18 2022-10-16 4 Google Ads 3690 300.0 250.0 #Be Bold. Be X 90.0 https://www.abcjewelry.com/women/handmade-jewe... 5686 Maharashtra 353 1015000.0 INR 4 Search 0.0 3112.549801 9019 514 INR INR 1 120.160485 Others INR Asia/Kolkata week_end bold jewelry 5.699080
21627 3131 22 2022-08-27 4 Google Ads 4715 300.0 250.0 #Be Bold. Be X 90.0 https://www.abcjewelry.com/collections/multi-s... 5686 Maharashtra 353 1015000.0 INR 1 Display 0.0 1682.021912 16390 513 INR INR 1 87.098777 Others INR Asia/Kolkata week_day minimalist jewelry 3.129957
20580 3130 23 2022-09-10 128 Facebook Ads 4420 300.0 250.0 #The Ultimate Fashion Statement with X 90.0 https://www.abcjewelry.com/collections/long-ne... 6334 Thailand 353 1010000.0 INR 64 Video 0.0 2241.035857 9774 512 INR INR 1 74.411266 Others INR Asia/Kolkata week_day multi-strand necklaces 5.238388
69851 3050 24 2022-10-25 128 Facebook Ads 14015 0.0 0.0 #Embrace Your Individuality with X -1.0 https://www.abcjewelry.com/collections/trendy-... 5641 Karnataka 353 805000.0 INR 64 Video 0.0 622.509960 3582 512 INR INR 1 24.563921 Others INR Asia/Kolkata week_day victorian jewelry 14.293691
18954 3051 10 2022-09-02 4 Google Ads 3955 300.0 250.0 #Be Bold. Be X 93.0 https://www.abcjewelry.com/women/statement-jew... 5641 Karnataka 353 5000.0 INR 8 Social 0.0 441.982072 6444 512 INR INR 1 66.835769 Retargeting INR Asia/Kolkata week_day minimalist jewelry 7.945376
19715 3099 7 2022-07-11 4 Google Ads 4160 300.0 250.0 #The X Factor - Fashion for the Fearless 90.0 https://www.abcjewelry.com/collections 6370 North 353 100000.0 INR 32 Mobile 0.0 1854.370132 18685 512 INR INR 1 165.776948 Others INR Asia/Kolkata week_day bridal jewelry 2.740166
19040 3053 28 2022-08-20 128 Facebook Ads 3980 300.0 250.0 #The Ultimate Fashion Statement with X 90.0 https://www.abcjewelry.com/collections 5641 Karnataka 353 5000.0 INR 32 Mobile 0.0 1245.019920 8744 511 INR INR 1 56.825992 Others INR Asia/Kolkata week_day fashionable jewelry 5.844007
17483 3052 21 2022-08-26 128 Facebook Ads 3540 300.0 250.0 #Be Bold. Be X 90.0 https://www.abcjewelry.com/women/costume-jewelry/ 6337 Kuala Lumpur 353 1015000.0 INR 1 Display 0.0 2019.422311 14057 511 INR INR 1 118.946097 Others INR Asia/Kolkata week_day handmade jewelry 3.635200
70468 3058 15 2022-10-15 128 Facebook Ads 14310 0.0 0.0 #Be Bold. Be X -1.0 https://www.abcjewelry.com/collections/handmad... 6328 Madhya Pradesh 353 115000.0 INR 32 Mobile 0.0 3735.059761 11218 511 INR INR 1 126.246230 Others INR Asia/Kolkata week_day costume jewelry sets 4.555179
70616 3099 17 2022-08-22 128 Facebook Ads 14350 0.0 0.0 #Be Bold. Be X -1.0 https://www.abcjewelry.com/collections/pearl-j... 6370 North 353 100000.0 INR 32 Mobile 0.0 1844.480158 13403 511 INR INR 1 89.222825 Others INR Asia/Kolkata week_day anklets 3.812579
17448 3052 13 2022-08-18 16 DV360 3535 300.0 250.0 #The X Factor - Fashion for the Fearless 90.0 https://www.abcjewelry.com/women/costume-jewelry/ 6337 Kuala Lumpur 353 1015000.0 INR 64 Video 0.0 2000.747012 12408 510 INR INR 1 88.701568 Others INR Asia/Kolkata week_day designer-inspired jewelry 4.110251
In [17]:
df['ext_service_name'].value_counts()
Out[17]:
Facebook Ads    24275
DV360           24171
Google Ads      24166
Name: ext_service_name, dtype: int64

Visualization Implementations¶

Reasonings are conveyed at the chart descriptions

Histogram chart¶

A histogram is used to illustrate the distribution of a dataset and displays which values are most frequent.

Reasons

  1. To calculate the probability of representation of any value of a continuous variable
  2. Helps to visualize whether the distribution is symmetric or skewed left or right.
  3. It can also show any outliers or gaps in the data.

The benchmarks for CTR ( Click through rate ) is 0.76% for Style & Fashion tags (Google) and 2.71% to be in Top 10% competition. Our CTR distribution lies between 0.76-2.71 for ABC company.

In [18]:
# Clicks per 100 Appearances Frequency Distribution
# set the xlabel and the fontsize
plt.xlabel("ctr", fontsize=15)

# set the ylabel and the fontsize
plt.ylabel("Frequency", fontsize=15)

# set the title of the plot
plt.title("Clicks per 100 Appearances Distribution", fontsize=15)

# plot the histogram for the target variable
plt.hist(df.loc[(df["ctr"]>=0.1) & (df["ctr"]<=0.76)]['ctr'])
plt.show()
In [19]:
# Filter the data and create a histogram
hist_data = df.loc[(df["ctr"]>0.1) & (df["ctr"]<=3.71)]['ctr']
fig = px.histogram(hist_data, nbins=20)

# Set the layout properties
fig.update_layout(
    title="Clicks per 100 Appearances Distribution",
    xaxis_title="cpa",
    yaxis_title="Frequency",
    font=dict(size=15),
    showlegend=False,    width=500, # set width to 500 pixels
    height=250, # set height to 500 pixels

)

fig.show()

SCATTER PLOT¶

Purpose:¶

  1. Identify easily visible patterns and relationships

  2. Detecting outliers

  3. Visualize trends over time

Conclusions¶

  1. As campaign length increases, impressions and clicks decrease

  2. Campaigns of longer duration have constant & low impressions and clicks

  3. The graphs below can show that most campaigns with less duration were newly created or paused due to poor performance.

  4. We can spot outliers in both the graphs which indicates sudden spikes in the impressions and clicks which maybe due to certain events such as festivals , social media popularity , etc . We can further analyse at what time of the day , on which days , in which season , festivals , national or public holidays the performance usually goes up.

Prettier Graph¶

In [20]:
import plotly.express as px

fig = px.scatter(df, x="no_of_days", y="impressions", trendline="ols",    width=750, # set width to 500 pixels
    height=500, # set height to 500 pixels
)
fig.show()
In [21]:
fig = px.scatter(df, x="no_of_days", y="clicks", trendline="ols",    width=750, # set width to 500 pixels
    height=500, # set height to 500 pixels
)
fig.show()

Conclusions¶

  1. Mobile campaigns are able to get higher clicks
  2. The performance of search campaigns needs to be improved
  3. Social campaigns are able to reach audiences more but unable to get conversions
In [22]:
# scatter plot : impressions vs clicks ( hue : channel_name )
sns.lmplot(x = "impressions", y = "clicks", data = df, fit_reg=False, hue='channel_name')
Out[22]:
<seaborn.axisgrid.FacetGrid at 0x7f80e60ecf40>
In [23]:
fig = px.scatter(df, x="impressions", y="clicks", color="channel_name",
                 hover_name="channel_name",
                 labels={"impressions": "Impressions", "clicks": "Clicks"},
                 title="Impressions vs Clicks by Channel Name")

fig.update_layout(
    font=dict(size=14),
    legend=dict(title=None),
    plot_bgcolor="white",
    margin=dict(l=80, r=20, t=60, b=80),
    width=750, # set width to 500 pixels
    height=500, # set height to 500 pixels
)


fig.show()

Conclusions¶

  1. Top 3 countries where the campaigns are running are India, Oman , Qatar & UAE. This means the our company mainly operates in the Middle Eastern Asian region .
  2. We can further find out performance metrics of each country vs budget they were alloted to gauge the relative performance and take more informed decision.

Making a more Readable Visualization¶

We need to group Indian states as one country.

In [24]:
#  calculate data
labels=df['advertiser_name'].value_counts().index,
values=df['advertiser_name'].value_counts(),
In [25]:
df_abridged=df
In [26]:
df.head()
Out[26]:
campaign_item_id no_of_days time ext_service_id ext_service_name creative_id creative_width creative_height search_tags template_id landing_page advertiser_id advertiser_name network_id approved_budget advertiser_currency channel_id channel_name network_margin campaign_budget_usd impressions clicks stats_currency currency_code exchange_rate media_cost_usd search_tag_cat cmi_currency_code timezone weekday_cat keywords ctr
0 2733 7 2022-05-01 128 Facebook Ads 1000 300.0 250.0 #The Power of X 90.0 https://www.abcjewelry.com/collections/boho-je... 4756 Web 190 400.0 SGD 32 Mobile 0.0 652.173913 837 8 SGD SGD 1 14.058514 Others SGD Asia/Singapore week_end delicate bracelets 0.955795
1 2733 8 2022-05-02 16 DV360 1000 300.0 250.0 #Be Bold. Be X 90.0 https://www.abcjewelry.com/women/costume-jewelry/ 4756 Web 190 400.0 SGD 8 Social 0.0 652.173913 2634 44 SGD SGD 1 99.633496 Others SGD Asia/Singapore week_day summer jewelry 1.670463
2 2733 9 2022-05-03 128 Facebook Ads 1000 300.0 250.0 #Embrace Your Individuality with X 90.0 https://www.abcjewelry.com/collections/boho-je... 4756 Web 190 400.0 SGD 8 Social 0.0 652.173913 2135 32 SGD SGD 1 109.419677 Others SGD Asia/Singapore week_day artisan jewelry 1.498829
3 2733 10 2022-05-04 128 Facebook Ads 1000 300.0 250.0 #Be Bold. Be X 90.0 https://www.abcjewelry.com/collections/rhinest... 4756 Web 190 400.0 SGD 64 Video 0.0 652.173913 2327 48 SGD SGD 1 115.209499 Others SGD Asia/Singapore week_day layered bracelets 2.062742
4 2733 11 2022-05-05 4 Google Ads 1000 300.0 250.0 #Be Bold. Be X 90.0 https://www.abcjewelry.com/collections/boho-je... 4756 Web 190 400.0 SGD 32 Mobile 0.0 652.173913 1538 20 SGD SGD 1 66.990104 Others SGD Asia/Singapore week_day minimalist jewelry 1.300390
In [27]:
India = ['Andhra Pradesh', 'Karnataka', 'Pan India','North', 'Gujarat', 'Orissa', 'Tamil Nadu', 'Maharashtra', 'West Bengal', 'Madhya Pradesh', 'Coimbatore', 'Bangalore', 'Chennai', 'Punjab', 'Delhi', 'Haryana', 'UP','Pali', 'Vadodara']

Oman = ['Muscat','Sohar']

Qatar = ['Doha']

UAE = ['Dubai', 'Abu Dhabi']

Bahrain = ['Manama']

Kuwait = ['Kuwait City', 'Al Ahmadi']

KSA = ['Jeddah']

Malaysia = ['Kuala Lumpur']

Singapore = ['Singapore']

USA = ['New York']

Thailand = ['Bangkok']

Egypt = ['Cairo', 'Luxor',  'Almaza Bay']

Bangladesh = ['Chattogram', 'Chandpur']

Ethiopia = ['Addis Ababa']
In [28]:
cities_by_country = {'India': India,
'Oman': Oman,
'Qatar': Qatar,
'UAE': UAE,
'Bahrain': Bahrain,
'Kuwait': Kuwait,
'KSA': KSA,
'Malaysia': Malaysia,
'Singapore': Singapore,
'USA': USA,
'Thailand': Thailand,
'Egypt': Egypt,
'Bangladesh': Bangladesh,
'Ethiopia': Ethiopia}
In [29]:
for country,cities in cities_by_country.items():
    df.loc[df['advertiser_name'].isin(cities), "advertiser_name"] = country
In [30]:
label = df['advertiser_name'].value_counts().index
name = df['advertiser_name'].value_counts()
In [31]:
# Create data for the Pie Chart
data = [go.Pie(labels=name.index,
               values=name,
               hole=0.4,
               textposition='inside',
               textinfo='label+percent',
               hoverinfo='label+percent+value')]

# Set layout for the Pie Chart
layout = go.Layout(title='Ad Campaigns running accross the globe (Percentage)',
                   showlegend=False,
                   legend=dict(orientation="h"),
                   width=1000,
                   height=500,)

# Create figure object
fig = go.Figure(data=data, layout=layout)

# Display the figure
pyo.iplot(fig)
In [32]:
data_2=df.groupby('advertiser_name')['clicks'].sum().sort_values()
In [33]:
# Create data for the Pie Chart
data = [go.Pie(labels=data_2.index,
               values=data_2,
               hole=0.4,
               textposition='inside',
               textinfo='label+percent',
               hoverinfo='label+percent+value')]

# Set layout for the Pie Chart
layout = go.Layout(title='Clicks across the Globe (%)',
                   showlegend=False,
                    width=1000,
                   height=500,)

# Create figure object
fig = go.Figure(data=data, layout=layout)

# Display the figure
pyo.iplot(fig)
In [34]:
data_3=df.groupby('advertiser_name')['impressions'].sum().sort_values()
In [35]:
# Create data for the Pie Chart
data = [go.Pie(labels=data_3.index,
               values=data_3,
               hole=0.4,
               textposition='inside',
               textinfo='label+percent',
               hoverinfo='label+percent+value')]

# Set layout for the Pie Chart
layout = go.Layout(title='Ad Appearances across the Globe (%)',
                   showlegend=False,
                    width=1000,
                   height=500,)

# Create figure object
fig = go.Figure(data=data, layout=layout)

# Display the figure
pyo.iplot(fig)
In [36]:
data_4=df.groupby('advertiser_name')['ctr'].mean().sort_values()
In [37]:
data_4
Out[37]:
advertiser_name
USA            1.159128
Singapore      1.207385
Qatar          1.236015
UAE            1.249283
Bahrain        1.262052
KSA            1.275042
Kuwait         1.294600
Oman           1.334730
Brand          1.384189
Web            1.497644
Egypt          1.685583
Malaysia       1.723401
Indore         2.397669
Bangladesh     2.948884
Thailand       3.792935
India          4.556899
Ethiopia       5.936385
Malad         24.718518
Name: ctr, dtype: float64